using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using XProject.Entity;
namespace XProject.DAO
{
    class AccountDAO:DBAccess
    {
        string SQL_SELECT_ID = "select max(AccountID) from Account";
        string SQL_SELECT_ALL = "select [AccountID],[Username],[Password],[Role],[Account].[PersonID],[Name] from Account inner join Person on Account.PersonID=Person.PersonID";
        string SQL_SELECT = "select [AccountID],[Username],[Password],[Role],[Person].[PersonID],[PersonImage],[Name],[DateOfBirth],[Gender] from Account inner join Person on Account.PersonID=Person.PersonID where Username=@username and Password=@password";
        string SQL_INSERT = "insert into [Account]([Username], [Password], [Role], [PersonID],[PersonImage]) values(@username, @password,@role,@personId,@image)";
        string SQL_UPDATE = "update [Account] set [Role]=@role, [PersonImage]=@image where AccountID=@accountId";
        string SQL_DELETE = "delete from account where AccountID=@accountID";
        string SQL_CHANGE_PASSWORD = "update [Account] set [Password]=@password where [Username]=@username";
        string SQL_COUNT = "select count(*) from [Account]";
        string SQL_SET_IMAGE = "update [Account] set PersonImage=@image where [AccountID] =@accountId";
        string message;
        public string Message
        {
            get { return message; }
        }
        public IList<Account> GetAllAccount()
        {
            IList<Account> list = new List<Account>();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_ALL, conn);
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    while (dr.Read())
                    {
                        Account acc = new Account();
                        acc.AccountID = dr.GetInt32(0);
                        acc.Username = dr.GetString(1);
                        acc.Password = dr.GetString(2);
                        acc.Role = dr.GetByte(3);
                        acc.Person.PersonID = dr.GetInt32(4);
                        acc.Person.Name = dr.GetString(5);


                        list.Add(acc);
                    }
                    Disconnect();

                }
                else { message = base.Message; };
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
            return list;
        }
        public bool GetAccount(Account account)
        {
            bool rs=false;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT, conn);
                command.Parameters.Add("@username", OleDbType.VarChar).Value = account.Username;
                command.Parameters.Add("@password", OleDbType.VarChar).Value = account.Password;
              
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    if (dr.Read())
                    {
                        account.AccountID = dr.GetInt32(0);
                        account.Role = dr.GetByte(3);
                        account.Person.PersonID = dr.GetInt32(4);
                        account.PersonImage = dr.GetString(5);
                        account.Person.Name = dr.GetString(6);
                        account.Person.DateOfBirth = dr.GetDateTime(7);
                        account.Person.Gender = dr.GetBoolean(8);
                        rs = true;
                    }
                    Disconnect();

                } 
                else { message = base.Message; };
            }
            catch (OleDbException ex) { message = ex.Message; }
            catch (Exception ex) { message = ex.Message; }
             return rs;
        }
        public int Insert(Account acc)
        {
            int effectedRows = 0;
            OleDbCommand command = new OleDbCommand(SQL_INSERT, conn);
            command.Parameters.Add("@username", OleDbType.VarChar).Value = acc.Username;
            command.Parameters.Add("@password", OleDbType.VarChar).Value = acc.Password;
            command.Parameters.Add("@role", OleDbType.TinyInt).Value = acc.Role;
            command.Parameters.Add("@personId", OleDbType.Integer).Value = acc.Person.PersonID;
            command.Parameters.Add("@iamge", OleDbType.VarChar).Value = acc.PersonImage;
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = SQL_SELECT_ID;
                acc.AccountID = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
            return effectedRows;
        }
        public int Delete(Account acc)
        {
            int effectedRows = 0;
            OleDbCommand command = new OleDbCommand(SQL_DELETE,conn);
            command.Parameters.Add("@accountID", OleDbType.Integer).Value = acc.AccountID;
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
            return effectedRows;
        }
        public int Update(Account acc)
        {
            int effectedRows = 0;
            OleDbCommand command = new OleDbCommand(SQL_UPDATE, conn);
            command.Parameters.Add("@role", OleDbType.TinyInt).Value = acc.Role;
            command.Parameters.Add("@image", OleDbType.VarChar).Value = acc.PersonImage;
            command.Parameters.Add("@accountId", OleDbType.Integer).Value = acc.AccountID;

            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
            return effectedRows;
        }
        public int ChangePassword(Account acc)
        {
            int effectedRows = 0;
            OleDbCommand command = new OleDbCommand(SQL_CHANGE_PASSWORD,conn);
            command.Parameters.Add("@password", OleDbType.VarChar).Value = acc.Password;
            command.Parameters.Add("@username", OleDbType.VarChar).Value = acc.Username;           
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
            return effectedRows;
        }
        public int SetImage(Account acc)
        {
            int effectedRows = 0;
            OleDbCommand command = new OleDbCommand(SQL_SET_IMAGE, conn);
            command.Parameters.Add("@image", OleDbType.VarChar).Value = acc.PersonImage;
            command.Parameters.Add("@accountId", OleDbType.Integer).Value = acc.AccountID;
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
            return effectedRows;
        }
        public int Count()
        {
            int count = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_COUNT, conn);
                if (Connect())
                {
                    count = int.Parse(command.ExecuteScalar().ToString());
                    Disconnect();
                }
                else { message = base.Message; };
            }
            catch (OleDbException ex) { message=ex.Message;}
            catch (Exception ex) { message = ex.Message; }
            return count;
        }
    }
}
